WITH w_user AS
  (SELECT t_org.code   AS org_code,
    t_org.name         AS org_name,
    T_FLAG.code        AS FLAG_CODE,
    T_FLAG.NAME        AS FLAG_NAME,
    T_SEX.code         AS SEX_CODE,
    T_SEX.NAME         AS SEX_NAME,
    T_DIPLOMA.code     AS DIPLOMA_CODE,
    T_DIPLOMA.NAME     AS DIPLOMA_NAME,
    T_PROFESSION.code  AS PROFESSION_CODE,
    T_PROFESSION.NAME  AS PROFESSION_NAME,
    T_MARRY_STATE.code AS MARRY_STATE_CODE,
    T_MARRY_STATE.NAME AS MARRY_STATE_NAME,
    T_FOLK.code        AS FOLK_CODE,
    T_FOLK.NAME        AS FOLK_NAME,
    T_COUNTRY.code     AS COUNTRY_CODE,
    T_COUNTRY.NAME     AS COUNTRY_NAME,
    T_PROVINCE.code    AS PROVINCE_CODE,
    T_PROVINCE.NAME    AS PROVINCE_NAME,
    T_CITY.code        AS CITY_CODE,
    T_CITY.NAME        AS CITY_NAME,
    T_DISTRICT.code    AS DISTRICT_CODE,
    T_DISTRICT.NAME    AS DISTRICT_NAME,
    t.*,
	Trunc(MONTHS_BETWEEN(SYSDATE, T.BIRTHDAY)/12) as now_age,
	TO_CHAR(T.END_DATE, 'yyyy-mm-dd') as end_date_str
  FROM c1_user t
  LEFT JOIN C1_ORG T_ORG
  ON ((sysdate BETWEEN T_ORG.BEGIN_DATE AND T_ORG.END_DATE)
  AND T_ORG.id = t.Org_ID)
  LEFT JOIN c1_dictionary T_FLAG
  ON ((sysdate BETWEEN T_FLAG.BEGIN_DATE AND T_FLAG.END_DATE)
  AND T_FLAG.id = t.FLAG_ID)
  LEFT JOIN c1_dictionary T_SEX
  ON ((sysdate BETWEEN T_SEX.BEGIN_DATE AND T_SEX.END_DATE)
  AND T_SEX.id = t.SEX_ID)
  LEFT JOIN c1_dictionary T_DIPLOMA
  ON ((sysdate BETWEEN T_DIPLOMA.BEGIN_DATE AND T_DIPLOMA.END_DATE)
  AND T_DIPLOMA.id = t.DIPLOMA_ID)
  LEFT JOIN c1_dictionary T_PROFESSION
  ON ((sysdate BETWEEN T_PROFESSION.BEGIN_DATE AND T_PROFESSION.END_DATE)
  AND T_PROFESSION.id = t.PROFESSION_ID)
  LEFT JOIN c1_dictionary T_MARRY_STATE
  ON ((sysdate BETWEEN T_MARRY_STATE.BEGIN_DATE AND T_MARRY_STATE.END_DATE)
  AND T_MARRY_STATE.id = t.MARRY_STATE_ID)
  LEFT JOIN c1_dictionary T_FOLK
  ON ((sysdate BETWEEN T_FOLK.BEGIN_DATE AND T_FOLK.END_DATE)
  AND T_FOLK.id = t.FOLK_ID)
  LEFT JOIN c1_dictionary T_COUNTRY
  ON ((sysdate BETWEEN T_COUNTRY.BEGIN_DATE AND T_COUNTRY.END_DATE)
  AND T_COUNTRY.id = t.COUNTRY_ID)
  LEFT JOIN c1_dictionary T_PROVINCE
  ON ((sysdate BETWEEN T_PROVINCE.BEGIN_DATE AND T_PROVINCE.END_DATE)
  AND T_PROVINCE.id = t.PROVINCE_ID)
  LEFT JOIN c1_dictionary T_CITY
  ON ((sysdate BETWEEN T_CITY.BEGIN_DATE AND T_CITY.END_DATE)
  AND T_CITY.id = t.CITY_ID)
  LEFT JOIN c1_dictionary T_DISTRICT
  ON ((sysdate BETWEEN T_DISTRICT.BEGIN_DATE AND T_DISTRICT.END_DATE)
  AND T_DISTRICT.id = t.DISTRICT_ID)
  WHERE T.ID        = :id
  ),
  w_last_Login_Date AS
  (SELECT t.CREATED_DATE last_Login_Date,
    row_number() over(order by t.CREATED_DATE DESC) rn
  FROM C1_ENTITY_LOG t
  WHERE t.code = 'login'
  AND EXISTS
    (SELECT 1 FROM w_user e WHERE e.id = t.entity_id
    )
  )
SELECT T.*,
  NVL((SELECT s.last_Login_Date FROM w_last_Login_Date s WHERE s.rn = 2
  ), sysdate) AS last_Login_Date ,
  (CASE WHEN (SYSDATE BETWEEN NVL(T.BEGIN_DATE, SYSDATE) AND NVL(T.END_DATE, SYSDATE)) THEN '1' ELSE '2' END) AS valid_code
FROM w_user t 